LogMessages and ReturnsRecords Properties Example

This example uses the LogMessages and ReturnsRecords properties to create a pass-through query that will return data and any messages generated by the remote server.

Sub LogMessagesX()

   Dim wrkJet As Workspace
   Dim dbsCurrent As Database
   Dim qdfTemp As QueryDef
   Dim prpNew As Property
   Dim rstTemp As Recordset

   ' Create Microsoft Jet Workspace object.
   Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

   Set dbsCurrent = wrkJet.OpenDatabase("DB1.mdb")

   ' Create a QueryDef that will log any messages from the 
   ' server in temporary tables.
   Set qdfTemp = dbsCurrent.CreateQueryDef("NewQueryDef")

   ' Note: The DSN referenced below must be configured to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
    qdfTemp.Connect = _
        "ODBC;DATABASE=pubs;DSN=Publishers"
   qdfTemp.SQL = "SELECT * FROM stores"
   qdfTemp.ReturnsRecords = True
   Set prpNew = qdfTemp.CreateProperty("LogMessages", _
      dbBoolean, True)
   qdfTemp.Properties.Append prpNew

   ' Execute query and display results.
   Set rstTemp = qdfTemp.OpenRecordset()

   Debug.Print "Contents of recordset:"
   With rstTemp
      Do While Not .EOF
         Debug.Print , .Fields(0), .Fields(1)
         .MoveNext
      Loop
      .Close
   End With

   ' Delete new QueryDef because this is a demonstration.
   dbsCurrent.QueryDefs.Delete qdfTemp.Name
   dbsCurrent.Close
   wrkJet.Close

End Sub